﻿Public Class frmCobroCuota
    Dim utilidades As New Utilidades
    Dim _idSolicitud As String
    Dim idPrestamo As String
    Dim idCliente As String
    Dim diasVencida As Integer
    Dim _sePresionoCobrar As Boolean = False

    Public Sub New(ByVal idPrestamo As String)
        Me.InitializeComponent()
        Me.idPrestamo = idPrestamo
    End Sub

    Private Sub frmCobroCuota_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim myAccesoDatos As New AccesoDatos
        Dim tabla As Data.DataTable
        Dim myConsulta As String

        myConsulta = "SELECT CL.ID FROM PRESTAMOS P INNER JOIN SOLICITUDES_PRESTAMOS S ON (P.ID_SOLICITUD = S.ID) "
        myConsulta = myConsulta & "INNER JOIN CLIENTES CL ON (S.ID_CLIENTE = CL.ID) AND P.ID = " & idPrestamo
        tabla = myAccesoDatos.ejecutarConsulta(myConsulta)
        Me.idCliente = Convert.ToString(tabla.Rows.Item(0).Item(0))

        myConsulta = "SELECT P.ID, C.NRO_CUOTA, CONVERT(varchar(10), CONVERT(money, C.MONTO)) AS VALOR, CONVERT(VARCHAR(10), "
        myConsulta = myConsulta & "C.FECHA_VENCIMIENTO, 103) AS FECHA_VENCIMIENTO, (CL.APELLIDO + ', ' + CL.NOMBRE) AS APELLIDO_NOMBRE "
        myConsulta = myConsulta & "FROM PRESTAMOS P INNER JOIN CUOTAS C ON (P.ID = C.ID_PRESTAMO) "
        myConsulta = myConsulta & "INNER JOIN SOLICITUDES_PRESTAMOS S ON (P.ID_SOLICITUD = S.ID) "
        myConsulta = myConsulta & "INNER JOIN CLIENTES CL ON (S.ID_CLIENTE = CL.ID) "
        myConsulta = myConsulta & "WHERE P.ID = " & idPrestamo
        myConsulta = myConsulta & " AND C.PAGADA = 'N' "
        myConsulta = myConsulta & " AND C.NRO_CUOTA < = ALL (SELECT NRO_CUOTA FROM CUOTAS WHERE PAGADA = 'N' AND ID_PRESTAMO = " & idPrestamo & ")"
        tabla = myAccesoDatos.ejecutarConsulta(myConsulta)

        Me.txtCliente.Text = Convert.ToString(tabla.Rows.Item(0).Item(4))
        Me.txtNroPrestamo.Text = Convert.ToString(tabla.Rows.Item(0).Item(0))
        Me.txtCuotas.Text = Convert.ToString(tabla.Rows.Item(0).Item(1))
        Me.txtVencimiento.Text = Convert.ToString(tabla.Rows.Item(0).Item(3))
        Me.txtMonto.Text = Convert.ToString(tabla.Rows.Item(0).Item(2))

        Dim fecha As String = Format(Today, "dd/MM/yyyy")
        Dim monto As Decimal = Convert.ToDouble(tabla.Rows.Item(0).Item(2))
        Dim intereses As Decimal = 0
        Dim total As Decimal
        diasVencida = DateDiff("d", Convert.ToString(tabla.Rows.Item(0).Item(3)), fecha)

        If diasVencida > 0 Then
            intereses = Math.Round(diasVencida * 0.2, 2)
        End If

        total = Math.Round(monto + intereses, 2)

        Me.txtInteres.Text = intereses
        Me.txtTotal.Text = total

    End Sub

    Private Sub frmSolicitudPrestamo_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        If _sePresionoCobrar = False Then
            If MessageBox.Show("No se cobrará la Cuota ¿Está seguro?", "Atención", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) = Windows.Forms.DialogResult.OK Then
                e.Cancel = False
            Else
                e.Cancel = True
            End If
        End If
    End Sub

    Private Sub cmdAceptar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAceptar.Click
        If MessageBox.Show("¿Está seguro que desea cobrar la Cuota?", "Importante", _
            MessageBoxButtons.OKCancel, MessageBoxIcon.Question) = Windows.Forms.DialogResult.OK Then

            Dim myAccesoDatos As New AccesoDatos
            Dim myConsulta As String

            ''Creo la Factura
            myConsulta = "INSERT INTO FACTURAS(ID_PRESTAMO, NRO_CUOTA, FECHA, INTERES, MONTO_TOTAL) VALUES ( "
            myConsulta = myConsulta & idPrestamo
            myConsulta = myConsulta & ", " & Me.txtCuotas.Text
            myConsulta = myConsulta & ", '" & utilidades.obtenerFechaFormatoSQLServer2008(Format(Today, "dd/MM/yyyy"))
            myConsulta = myConsulta & "', " & Me.txtInteres.Text
            myConsulta = myConsulta & ", " & Me.txtTotal.Text & ")"
            myAccesoDatos.ejecutarConsulta(myConsulta)

            ''Actualizo la Cuota a Pagada
            myConsulta = "UPDATE CUOTAS SET PAGADA = 'S' WHERE ID_PRESTAMO = " & idPrestamo & " AND NRO_CUOTA =" & Me.txtCuotas.Text
            myAccesoDatos.ejecutarConsulta(myConsulta)

            ''Si todas las cuotas del prestamo se encuentran pagadas lo marco como pagado
            Dim tabla As Data.DataTable
            myConsulta = "SELECT P.ID FROM PRESTAMOS P INNER JOIN CUOTAS C ON (P.ID = C.ID_PRESTAMO)AND C.PAGADA = 'N' WHERE P.ID = " & idPrestamo
            tabla = myAccesoDatos.ejecutarConsulta(myConsulta)
            If (tabla.Rows.Count = 0) Then
                myConsulta = "UPDATE PRESTAMOS SET PAGADO = 'S' WHERE ID = " & idPrestamo
                myAccesoDatos.ejecutarConsulta(myConsulta)
            End If

            ''Actualizo el Puntaje del Cliente
            Dim puntaje As Integer
            tabla = Nothing
            myConsulta = "SELECT PUNTAJE FROM CLIENTES WHERE ID = " & idCliente
            tabla = myAccesoDatos.ejecutarConsulta(myConsulta)
            puntaje = Convert.ToInt32(tabla.Rows.Item(0).Item(0))

            If diasVencida > 0 Then
                puntaje = puntaje - 5
            Else
                puntaje = puntaje + 5
            End If

            myConsulta = "UPDATE CLIENTES SET PUNTAJE = " & puntaje & " WHERE ID = " & idCliente
            myAccesoDatos.ejecutarConsulta(myConsulta)

            ''Actualizo el Estado del Cliente
            Me.actualizarEstadoCliente()

            ''Muestro la Factura
            Dim ds As New dsFactura
            Dim report As New rptFactura

            myConsulta = "SELECT P.ID as prestamo, ('Cuota Nro. '  + CONVERT(varchar(10), C.NRO_CUOTA)) as detalle,  "
            myConsulta = myConsulta & "CONVERT(varchar(10), CONVERT(money, C.MONTO)) AS monto,  "
            myConsulta = myConsulta & "CONVERT(VARCHAR(10), getDate(), 103) AS fecha, (CL.APELLIDO + ', ' + CL.NOMBRE) AS cliente, "
            myConsulta = myConsulta & "CONVERT(varchar(10), CONVERT(money, F.INTERES)) AS intereses, "
            myConsulta = myConsulta & "CONVERT(varchar(10), CONVERT(money, F.MONTO_TOTAL)) AS total, F.ID as nro "
            myConsulta = myConsulta & "FROM PRESTAMOS P INNER JOIN CUOTAS C ON (P.ID = C.ID_PRESTAMO) "
            myConsulta = myConsulta & "INNER JOIN SOLICITUDES_PRESTAMOS S ON (P.ID_SOLICITUD = S.ID) "
            myConsulta = myConsulta & "INNER JOIN CLIENTES CL ON (S.ID_CLIENTE = CL.ID) "
            myConsulta = myConsulta & "INNER JOIN FACTURAS F ON (F.ID_PRESTAMO = C.ID_PRESTAMO AND F.NRO_CUOTA = C.NRO_CUOTA) "
            myConsulta = myConsulta & "WHERE P.ID = " & idPrestamo
            myConsulta = myConsulta & " AND F.NRO_CUOTA = " & Me.txtCuotas.Text

            myAccesoDatos.ejecutarConsultaInforme(myConsulta, ds.Factura)
            report.SetDataSource(ds)

            Dim frmReportFacura As New frmReportFacura
            frmReportFacura.crvFactura.ReportSource = report
            frmReportFacura.ShowDialog()

            _sePresionoCobrar = True

            Try
                frmPrincipal.frmGestionPrestamo.buscar(frmGestionPrestamo.armarWhereConsulta)
            Catch ex As Exception

            End Try

            Me.Close()
        End If

    End Sub

    Private Sub actualizarEstadoCliente()
        Dim myConsulta As String
        Dim myAccesoDatos As New AccesoDatos
        If elClienteAdeuda() = False Then
            myConsulta = "UPDATE CLIENTES SET ID_ESTADO_CLIENTE = 1 "
            myConsulta = myConsulta + "WHERE ID =" + idCliente
            myAccesoDatos.ejecutarConsulta(myConsulta)
        End If

    End Sub

    Private Function elClienteAdeuda() As Boolean
        Dim hayClientes As Boolean = False
        Dim tabla As New Data.DataTable
        Dim myAccesoDatos As New AccesoDatos
        Dim myConsulta As String

        myConsulta = "SELECT DISTINCT C.NRO_CUOTA  "
        myConsulta = myConsulta + "FROM PRESTAMOS P INNER JOIN CUOTAS C ON (P.ID = C.ID_PRESTAMO) "
        myConsulta = myConsulta + "INNER JOIN SOLICITUDES_PRESTAMOS S ON (P.ID_SOLICITUD = S.ID) "
        myConsulta = myConsulta + "WHERE C.PAGADA = 'N' AND getDate() > C.FECHA_VENCIMIENTO "
        myConsulta = myConsulta + "AND s.ID_CLIENTE = " + idCliente

        tabla = myAccesoDatos.ejecutarConsulta(myConsulta)

        If ((Not tabla Is Nothing) And (tabla.Rows.Count > 0)) Then
            If Not Convert.ToInt32(tabla.Rows.Item(0).Item(0)) = 0 Then
                hayClientes = True
            Else
                hayClientes = False
            End If
        Else
            hayClientes = False
        End If

        Return hayClientes
    End Function


    Private Sub cmdCancelar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdCancelar.Click
        _sePresionoCobrar = False
        Me.Close()
    End Sub
End Class